package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import View.OrderItemView;

import BO.Item;
import BO.Order;
import BO.OrderItem;

public class OrderDB extends OrderItem{
	
	public OrderDB(Item item, int amount) {
		super(item, amount);
	}

	/**
	 * Place an order for some items. Will only for if you got enough of items in stock.
	 * @param items - All the items you are interested in buying.
	 * @param userId - The id of the user that's going to buy the stuffs.
	 * @return - True of false, depending on if the order was successful.
	 */
	public static boolean orderStuffs(ArrayList<OrderItemView> items, int userId){
		Connection connection = ConnectionPool.getConnection();
		boolean commit = true;
		try {
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();
			//Create a order
			stmt.executeUpdate("insert into T_ORDER (user_id,status) values (" + userId + ",0);",Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = stmt.getGeneratedKeys();
			int orderId = 0;
			if(rs.next())//Get the new orders id
				orderId = rs.getInt(1);
			if(orderId != 0){
				for(int i = 0; i < items.size(); i++){
					OrderItemView order = items.get(i);
					int itemId = order.getItem().getItemId();
					//Get the amount in stock
					rs = stmt.executeQuery("select amount from T_ITEM where item_id =" + itemId + ";");
					if(rs.next()){
						int amount = rs.getInt("amount"); 
						if(amount < order.getAmount()){//Check if stock is high enough
							commit = false;
							break;
						}else{
							//Update amount
							stmt.executeUpdate("update T_ITEM set amount = " + (amount- order.getAmount()) + " where item_id =" + itemId + ";");
							//Move items into orderItems
							stmt.executeUpdate("insert into T_ORDERITEMS (order_id,item_id,amount) values (" + orderId + "," + itemId + ","+ order.getAmount() + ");");
						}
					}else{
						commit = false;
						break;
					}
				}
			}else{
				commit = false;
			}
			if(commit){
				connection.commit();
			}else{
				connection.rollback();
			}
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			if(connection != null){
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}
		ConnectionPool.releaseConnection(connection);
		return commit;
	}
	
	/**
	 * Update the status of an order to packed.
	 * @param orderId - The order id that will be packed.
	 */
	public static void packOrder(int orderId){
		Connection connection = ConnectionPool.getConnection();
		try{
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("update T_ORDER set status = 1 where order_id = " + orderId + ";");
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
	}
	
	/**
	 * Get all orders that are not yet packed.
	 * @return - All orders that need to be packed.
	 */
	public static ArrayList<Order> getAllUnpackedOrders(){
		Connection connection = ConnectionPool.getConnection();
		ArrayList<Order> orders = new ArrayList<Order>();
		try{
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_Order o,T_ORDERITEMS i where o.order_id = i.order_id and o.status = 0;");
			orders = getOrderFromResultSet(rs);
			rs.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
		return orders;
	}
	
	/**
	 * Get all the orders a user got, both packed and unpacked.
	 * @param userId - The id of the user.
	 * @return - All orders the user have.
	 */
	public static ArrayList<Order> getAllOrderFromUser(int userId){
		Connection connection = ConnectionPool.getConnection();
		ArrayList<Order> order = new ArrayList<Order>();
		try{
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_ORDER o,T_ORDERITEMS i where o.order_id = i.order_id and o.user_id = " + userId + ";");
			order = getOrderFromResultSet(rs);
			rs.close();
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
		return order;
	}
	
	/*
	 * Help method for getting order status.
	 * @param rs - ResultSet to look for info in.
	 * @return - All orders that could be found.
	 * @throws SQLException - If something goes terrible wrong :p
	 */
	private static ArrayList<Order> getOrderFromResultSet(ResultSet rs) throws SQLException{
		ArrayList<Order> orders = new ArrayList<Order>();
		boolean first = true;
		int previous = -1;
		int orderId = 0;
		int userId = 0;
		int status = 0;
		int itemId = 0;
		int amount = 0;
		ArrayList<OrderItem> items = new ArrayList<OrderItem>();
		while(rs.next()){
			orderId = rs.getInt("order_id");
			if(first){
				previous = orderId;
				first = false;
			}
			if(previous != orderId){
				orders.add(new Order(previous,userId,status,items));
				items = new ArrayList<OrderItem>();
				previous = orderId;
			}
			userId = rs.getInt("user_id");
			status = rs.getInt("status");
			itemId = rs.getInt("item_id");
			amount = rs.getInt("amount");
			Item item = Item.getItemById(itemId);
			items.add(new OrderDB(item,amount));
		}
		if(items.size() != 0)
			orders.add(new Order(orderId,userId,status,items));
		return orders;
	}

}
